﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ColorWanted.Plugin.ExcelToJSON
{
    internal static class ExcelLoader
    {
        public static List<SheetData> Load(string filename)
        {
            using (var file = File.OpenRead(filename))
            {
                IWorkbook workbook;

                if (filename.ToLower().EndsWith(".xls"))
                {
                    workbook = new HSSFWorkbook(file);
                }
                else if (filename.ToLower().EndsWith(".xlsx"))
                {
                    workbook = new XSSFWorkbook(file);
                }
                else
                {
                    throw new Exception("不支持的文件类型");
                }

                var data = new List<SheetData>();

                for (int i = 0; i < workbook.NumberOfSheets; i++)
                {
                    data.Add(LoadSheet(workbook.GetSheetAt(i)));
                }

                return data;
            };
        }

        private static SheetData LoadSheet(ISheet sheet)
        {
            var data = new SheetData
            {
                Name = sheet.SheetName
            };

            for (int i = 0; i < sheet.LastRowNum; i++)
            {
                var row = sheet.GetRow(i);
                data.Rows.Add(row.Cells.Select<ICell, object>(cell =>
                {
                    return GetCellValue(cell, cell.CellType);
                }));
            }
            return data;
        }

        private static object GetCellValue(ICell cell, CellType cellType)
        {

            switch (cellType)
            {
                case CellType.Error:
                    //return cell.ErrorCellValue;
                    return null;
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Boolean:
                    return cell.BooleanCellValue;
                case CellType.Blank:
                    return null;
                case CellType.Numeric:
                    return cell.NumericCellValue;
                case CellType.Formula:
                    return GetCellValue(cell, cell.CachedFormulaResultType);
                default:
                    return cell.StringCellValue;
            }
        }

    }
}
